from typing import List, Dict, Any, Optional, Tuple
from apps.models.models import Training_Programs, Training_Categories, User, Student, Student_trainings
from apps.schemas.schemas import ProgramCreateRequest, ProgramUpdateRequest
from tortoise.transactions import in_transaction


async def get_all_programs(page: int = 1, page_size: int = 10) -> Tuple[int, List[Dict[str, Any]]]:
    """
    获取所有训练项目列表（分页）
    :param page: 页码
    :param page_size: 每页数量
    :return: (总数, 训练项目列表)
    """
    # 计算偏移量
    offset = (page - 1) * page_size
    # 获取总数
    total = await Training_Programs.all().count()
    # 查询当前页的课程数据
    programs = await Training_Programs.all().offset(offset).limit(page_size).values(
        "program_id",
        "name",
        "description",
        category_id="category_id_id"  # 外键字段名是 category_id_id
    )

    return total, list(programs)


async def get_student_programs_count(student_id: int) -> int:
    """
    通过学生ID获取该学生所有学习的科目数量
    :param student_id: 学生ID
    :return: 科目数量
    """
    # 检查学生是否存在
    student = await Student.get_or_none(student_id=student_id)
    if not student:
        raise ValueError("Student not found")

    # 查询该学生所有学习的科目数量
    programs_count = await Student_trainings.filter(student_id=student_id).count()
    return programs_count


async def create_program(data: ProgramCreateRequest) -> Dict[str, Any]:
    """
    创建新的训练项目
    :param data: 训练项目创建请求数据
    :return: 创建的训练项目信息
    """
    # 检查类名是否存在
    existing_program = await Training_Programs.get_or_none(name=data.name)
    if existing_program:
        raise ValueError("Program name already exists")

    try:
        # 获取当前用户
        current_user = await User.get_or_none(user_id=data.create_by_id)
        if not current_user:
            raise ValueError("User not found")

        # 获取 category 实例
        category = await Training_Categories.get_or_none(category_id=data.category_id)
        if not category:
            raise ValueError("Category not found")

        async with in_transaction() as connection:
            program = await Training_Programs.create(
                name=data.name,
                description=data.description,
                create_by=current_user,  # 传入 User 实例，不是 user_id
                category_id=category,
                using_db=connection
            )

        return {
            "status": 201,
            "message": "Program created successfully",
            "data": {
                "program_id": program.program_id,
                "name": program.name,
                "description": program.description,
                "category_id": program.category_id_id,
                "create_by_id": program.create_by_id  # 返回创建者ID
            }
        }

    except Exception as e:
        raise Exception(f"Error creating program: {str(e)}")


async def update_program(program_id: int, data: ProgramUpdateRequest) -> Dict[str, Any]:
    """
    更新训练项目
    :param program_id: 训练项目ID
    :param data: 训练项目更新请求数据
    :return: 更新结果
    """
    # 查询 Program
    program = await Training_Programs.get_or_none(program_id=program_id)
    if not program:
        raise ValueError("Program not found")

    update_data = data.dict(exclude_unset=True)  # 只获取提供的字段

    # 更新 Program 表
    if any(k in update_data for k in ['name', 'description']):
        await program.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ['name', 'description']
        }).save()

    # 更新 category_id（如果提供了）
    if 'category_id' in update_data:
        category = await Training_Categories.get_or_none(category_id=update_data['category_id'])
        if not category:
            raise ValueError("Category not found")
        program.category_id = category  # 或者使用 program.category_id_id = update_data['category_id']
        await program.save()

    return {"status": 200, "message": "Program updated successfully"}


async def delete_program(program_id: int) -> Dict[str, Any]:
    """
    删除训练项目
    :param program_id: 训练项目ID
    :return: 删除结果
    """
    program = await Training_Programs.get_or_none(program_id=program_id)
    if not program:
        raise ValueError("Program not found")

    await program.delete()
    return {"status": 200, "message": "Program deleted successfully"}
